/*
*/
ALTER PROC SP_MOVE_INVENTORY_TO_CONSIGNMENT_LOCATION ( @CUSTOMER_SHIPING_ID INT, @ERROR_MSG VARCHAR(200) OUT ) AS
 BEGIN
	DECLARE @IS_CONSIGNMENT_SALES INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @FG_LOCATION_ID INT
	DECLARE @VENDOR_NUMBER VARCHAR(50)
	DECLARE @LOT_CURSOR CURSOR

	DECLARE @LOT_ID INT
	DECLARE @PROCESSSTEP_ID INT
	DECLARE @LOT_NUMBER VARCHAR(50)
	DECLARE @FAB_LOT_NUMBER VARCHAR(50)
	DECLARE @VENDOR_LOT_NUMBER VARCHAR(50)
	DECLARE @LOT_CATEGORY VARCHAR(50)
	DECLARE @QTY_PULLED VARCHAR(50)
	DECLARE @FG_VENDOR_NUMBER VARCHAR(50)

	SELECT @IS_CONSIGNMENT_SALES = SO.SO_IS_CONSIGNMENT, @CUSTOMER_ID = SO.CUSTOMER_ID FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO WHERE CST.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMERSHIPPING_ID = @CUSTOMER_SHIPING_ID
	IF ( @IS_CONSIGNMENT_SALES = 0 )
	 BEGIN
		--NEED TO MOVE THE SHIPPED INVENTORY TO THE CUSTOMER VENDOR LOCATION
		SELECT @FG_VENDOR_NUMBER = C_NUMBER FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID
		SELECT 	@FG_LOCATION_ID = LOCATION_ID FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID 
											AND V.V_NUMBER = @FG_VENDOR_NUMBER 											
											AND L.L_TYPE = 'Finished Goods'
											AND L.L_Is_Deleted='N' 
		IF ( @FG_LOCATION_ID IS NULL )
		 BEGIN
			SET @ERROR_MSG = 'CONSIGNMENT WAREHOUSE NOT FOUND FOR CUSTOMER: "' + @FG_VENDOR_NUMBER + '"'
			RETURN
		 END
		SET @LOT_CURSOR = CURSOR FOR SELECT LT.PROCESSSTEP_ID, LT.LOT_NUMBER, LT.FAB_LOT_NUMBER, LT.VENDOR_LOT_NUMBER, LT.LOT_CATEGORY, CSLNT.CSLNT_QTY_PULL 
						FROM LOT_TABLE LT, CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT 							
						WHERE CST.CUSTOMERSHIPPING_ID = @CUSTOMER_SHIPING_ID 
						AND CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
						AND CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
						AND LT.LOT_ID = CSLNT.LOT_ID 
						AND CSLNT.CSLNT_QTY_PULL > 0
		
		OPEN @LOT_CURSOR
		FETCH NEXT FROM @LOT_CURSOR INTO @PROCESSSTEP_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @LOT_CATEGORY, @QTY_PULLED
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			DECLARE @QTY_IN INT
			SET @LOT_ID = NULL
			SET @QTY_IN = 0

			SELECT @LOT_ID = LOT_ID, @QTY_IN = QTY_IN FROM LOT_TABLE WHERE LOCATION_ID = @FG_LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
			IF ( @LOT_ID IS NULL )--LOT NOT EXISTS IN THE WAREHOUSE
			 BEGIN		
				INSERT INTO LOT_TABLE ( PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_ENTERED_BY, LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, TIMESTAMP )
						VALUES ( @PROCESSSTEP_ID, @FG_LOCATION_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, 0, @LOT_CATEGORY, @QTY_PULLED, CONVERT(VARCHAR,CURRENT_TIMESTAMP,101), 'Running', CURRENT_TIMESTAMP )
				SELECT @LOT_ID = @@IDENTITY
				INSERT INTO INVENTORY_LOT ( LOT_ID, IL_DATE_IN, IL_QTY_IN, TIMESTAMP ) 
						VALUES ( @LOT_ID, CONVERT(VARCHAR,CURRENT_TIMESTAMP,101), @QTY_PULLED, CURRENT_TIMESTAMP )
			 END
			ELSE --LOT EXISTS IN THE WAREHOUSE SO WE NEED TO ADD THE QTY TO THE EXISTING
			 BEGIN
				INSERT INTO LOT_TABLE_HISTORY(LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_ENTERED_BY, LOT_MANUAL_HOLD, LOT_CATEGORY, WAFER_QTY_IN, WAFER_QTY_OUT, QTY_IN, QTY_OUT, DATE_IN, DATE_OUT, STAGE_NAME, STAGE_POSITION, LOT_CURRENT_STATUS, LOT_COMMENTS, TIMESTAMP, LOT_SPLIT, LOT_MERGE, GOOD_DIE_QTY, LOT_DATE_CODE, LOT_DROP_OUT, SHOP_ORDER_NUMBER, PO_NUMBER, LOT_COST, LOT_CUMMULATIVE_COST)
					SELECT LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, LOT_ENTERED_BY, LOT_MANUAL_HOLD, LOT_CATEGORY, WAFER_QTY_IN, WAFER_QTY_OUT, QTY_IN, QTY_OUT, DATE_IN, DATE_OUT, STAGE_NAME, STAGE_POSITION, LOT_CURRENT_STATUS, LOT_COMMENTS, CURRENT_TIMESTAMP, LOT_SPLIT, LOT_MERGE, GOOD_DIE_QTY, LOT_DATE_CODE, LOT_DROP_OUT, SHOP_ORDER_NUMBER, PO_NUMBER, LOT_COST, LOT_CUMMULATIVE_COST FROM LOT_TABLE WHERE LOT_ID = @LOT_ID 
				UPDATE LOT_TABLE SET QTY_IN = (@QTY_IN+@QTY_PULLED), LOT_CURRENT_STATUS = 'Running' WHERE LOT_ID = @LOT_ID
				UPDATE INVENTORY_LOT SET IL_QTY_IN = (@QTY_IN+@QTY_PULLED), IL_QTY_BALANCE = (ISNULL(IL_QTY_BALANCE,@QTY_IN)+@QTY_PULLED) WHERE LOT_ID = @LOT_ID
			 END
			
			SET @LOT_ID = NULL
			SET @PROCESSSTEP_ID = NULL
			SET @LOT_NUMBER = NULL
			SET @FAB_LOT_NUMBER = NULL
			SET @VENDOR_LOT_NUMBER = NULL
			SET @LOT_CATEGORY = NULL
			SET @QTY_PULLED = NULL
			FETCH NEXT FROM @LOT_CURSOR INTO @PROCESSSTEP_ID, @LOT_NUMBER, @FAB_LOT_NUMBER, @VENDOR_LOT_NUMBER, @LOT_CATEGORY, @QTY_PULLED
		 END
		CLOSE @LOT_CURSOR
		DEALLOCATE @LOT_CURSOR
	 END
 END